﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace MJCOMMEN_CS.DBUtility
{
  public class Sqlopenhander:DBopenhandler
    {

      static string constr = System.Configuration.ConfigurationManager.AppSettings["DBString"];
      public Sqlopenhander()
      {
          SqlConnection con = new SqlConnection(constr);
          conn = con;
          conn.Open();
          cmd = conn.CreateCommand();
          da=new SqlDataAdapter();
      }
      /// <summary>
      /// 产生SqlCommand对象所需的查询参数。override是重写的意思重写父类的构造函数
      /// cmd中的commandText（sql语句）中需要提供该参数。
      ///说白了就是给数据库语句中的参数赋值。
      ///cmd.Parameters.AddWithValue("@p_StationID", s_ID);给前面cmd添加一个名字为@p_StationID的参数，赋值为s_ID.
      /// </summary>
      public override void GenParameters()
      {
          SqlCommand sqlCmd = (SqlCommand)cmd;
          if (this.alFieldItems.Count > 0)
          {
              for (int i = 0; i < alFieldItems.Count; i++)
              {
                  sqlCmd.Parameters.AddWithValue("@para" + i.ToString(), ((DBkeyitem)alFieldItems[i]).fieldvalue.ToString());
                 
              }
          }
          if (this.alSqlCmdParameters.Count > 0)
          {
              for (int i = 0; i < this.alSqlCmdParameters.Count; i++)
              {
                  sqlCmd.Parameters.AddWithValue(((DBkeyitem)alSqlCmdParameters[i]).fieldname.ToString(), ((DBkeyitem)alSqlCmdParameters[i]).fieldvalue.ToString());
              }
          }
          if (this.alConditionParameters.Count > 0)
          {
              for (int i = 0; i < this.alConditionParameters.Count; i++)
              {
                  sqlCmd.Parameters.AddWithValue(((DBkeyitem)alConditionParameters[i]).fieldname.ToString(), ((DBkeyitem)alConditionParameters[i]).fieldvalue.ToString());
              }
          }
      }

      /// <summary>
      ///获取分页数据表
      /// </summary>
      /// <param name="TableName">表名</param>
      /// <param name="FieldKey"></param>
      /// <param name="PageCurrent">当前页</param>
      /// <param name="PageSize">一页的有多少条数据</param>
      /// <param name="FieldShow">列名</param>
      /// <param name="FieldOrder">排列顺序</param>
      /// <param name="Where">条件</param>
      /// <param name="RecordCount">有多少条数据</param>
      /// <returns></returns>
      /// SELECT 列名称 FROM 表名称
      /// limit是从什么地方开始取多少条数据
      public static DataTable QueryPagedDt(string TableName, string FieldKey, int PageCurrent, int PageSize, string FieldShow, string FieldOrder, string Where, ref int RecordCount)
      {
          if (FieldOrder != "")
          {
              FieldOrder = " order by " + FieldOrder;
          }
          //计算有多少的数目
          //string   sql   =   "select   count(*)   from   userinfo   where   uName= ' "+userName+ " ' " 
          string SqlTT = "select count(*) from " + TableName + " where " + Where;
          DataTable DtTT = QueryDt(SqlTT);
          int.TryParse(DtTT.Rows[0][0].ToString(), out RecordCount);

          if (PageCurrent < 1)
          {
              PageCurrent = 1;
          }
          int limit = (PageCurrent - 1) * PageSize;
          string Sql = "select  top " + PageSize + " *  from  " + TableName + "  where  " + FieldShow + "  not  in " + "(" + " select  top " + limit + " " + FieldShow + "  from " + TableName +" where "+ Where+FieldOrder+")" + " and " + Where + FieldOrder;
          DataTable Dt = QueryDt(Sql);
          return Dt;
      }

      /// <summary>
      ///从数据库中获取数据表
      /// </summary>
      public static DataTable QueryDt(string Sql)
      {
          SqlConnection conn = new SqlConnection(constr);
          conn.Open();
          SqlDataAdapter da = new SqlDataAdapter(Sql, conn);
          DataSet ds = new DataSet();
          da.Fill(ds);
          conn.Close();
          return ds.Tables[0];
      }
    }
}
